/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package src.com.dados.pedagogico;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author Giane
 */
public class TurmaDao {

    public void inserirTurma(String codigo, String vagas, String dataInicio, String dataFim, String idioma, String nivel, String dia1, String dia2, String horaInicio, String horaFim, String professor) throws SQLException {

        Connection conexao;
        String id_idioma = id_idioma(idioma);
        String id_prof = id_professor(professor);
        String id_niv = id_nivel(nivel);
        System.out.print(id_idioma);
        String insert_turma = "INSERT INTO `bdeng2`.`tur_turma` (`tur_codigo`, `tur_vagas`, `tur_dt_inicio`, `tur_dt_fim`, `tur_dia1`, `tur_dia2`, `tur_hr_inico`, `tur_hr_fim`, `prof_professor_prof_id`, `idm_idioma_idm_id`, `nvl_nivel_nvl_id`)"
                + "           VALUES (" + codigo + "," + vagas + ",'" + dataInicio + "','" + dataFim + "','" + dia1 + "','" + dia2 + "','" + horaInicio + "','" + horaFim + "',"
                + id_prof + "," + id_idioma + "," + id_niv + ");";
        System.out.println(insert_turma);
        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");


        Statement stmt2 = (Statement) conexao.createStatement();
        stmt2.execute(insert_turma);
        conexao.close();





    }

    public String id_idioma(String idioma) throws SQLException {
        Connection conexao;
        String result1 = null;
        String select_idm = "select idm_id from idm_idioma where idm_descricao like '" + idioma + "';";
        System.out.println(select_idm);

        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_idm);
        ResultSet rs = stmt.executeQuery();



        while (rs.next()) {
            result1 = rs.getString(1);
        }

        conexao.close();
        return result1;
    }

    public String id_professor(String professor) throws SQLException {
        Connection conexao;
        String result2 = null;
        String select_prof = "SELECT prof_id FROM prof_professor,pes_pessoa where pes_pessoa_pes_id = pes_id and pes_nome like '" + professor + "';";
        System.out.println(select_prof);

        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_prof);
        ResultSet rs = stmt.executeQuery();



        while (rs.next()) {
            result2 = rs.getString(1);
        }

        conexao.close();
        return result2;

    }

    public String id_nivel(String nivel) throws SQLException {
        Connection conexao;
        String result3 = null;
        String select_niv = "SELECT nvl_id FROM nvl_nivel where nvl_nome like '" + nivel + "' ;";
        System.out.println(select_niv);

        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_niv);
        ResultSet rs = stmt.executeQuery();



        while (rs.next()) {
            result3 = rs.getString(1);
        }

        conexao.close();
        return result3;

    }

    public String id_turma(String nome) throws SQLException {
        Connection conexao;
        String result3 = null;
        String select_niv = "SELECT tur_id_turma FROM tur_turma where tur_codigo like '" + nome + "' ;";
        System.out.println(select_niv);

        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_niv);
        ResultSet rs = stmt.executeQuery();



        while (rs.next()) {
            result3 = rs.getString(1);
        }

        conexao.close();
        return result3;

    }

    public void removerTurma(String codTurma) throws SQLException {
        String select_remove = null;

        select_remove = "DELETE FROM `bdeng2`.`tur_turma` WHERE `tur_codigo`= " + codTurma + ";";

        System.err.println(select_remove);
        Connection conexao;

        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_remove);

        stmt.execute();

        conexao.close();
    }

    public String verificarTurmaMatricula(String codTurma) throws SQLException {
        String select_count = null;

        select_count = "SELECT count(tur_id_turma) FROM tur_turma,alu_aluno,mat_matricula where tur_turma_tur_id = tur_id_turma and alu_aluno_alu_matricula = alu_matricula and `tur_codigo`= " + codTurma + " ;";


        Connection conexao;
        String result = "0";


        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_count);
        ResultSet rs = stmt.executeQuery();


        while (rs.next()) {
            result = rs.getString(1);
        }


        conexao.close();
        return result;
    }

    public String verificaTurmaExiste(String codigo) throws SQLException {

        String select_count = "SELECT count(tur_id_turma) FROM tur_turma where`tur_codigo`= '" + codigo + "' ;";
        //System.out.println("TURMA: "+select_count);
        Connection conexao;
        String result = "0";


        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_count);
        ResultSet rs = stmt.executeQuery();


        while (rs.next()) {
            result = rs.getString(1);
        }


        conexao.close();
        return result;
    }

    public String verificaProfessorExiste(String professor) throws SQLException {
        String select_count = "SELECT count(prof_id) FROM prof_professor, pes_pessoa where pes_pessoa_pes_id = pes_id and pes_nome = '" + professor + "';";

        Connection conexao;
        String result = "0";


        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_count);
        ResultSet rs = stmt.executeQuery();


        while (rs.next()) {
            result = rs.getString(1);
        }


        conexao.close();
        return result;
    }

    public String retornaIdTurma(String mtr_aluno) throws SQLException {
        Connection conexao;
        String result3 = null;
        String select_niv = "SELECT distinct tur_turma_tur_id FROM bdeng2.mat_matricula where alu_aluno_alu_matricula = '" + mtr_aluno + "' ;";
        System.out.println(select_niv);

        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_niv);
        ResultSet rs = stmt.executeQuery();



        while (rs.next()) {
            result3 = rs.getString(1);
        }

        conexao.close();
        return result3;
    }

    public ArrayList<String> retornaCodTurma(String dia) throws SQLException {
        Connection conexao;
        ArrayList<String> result = new ArrayList<String>();
        String select_ano = "select tur_codigo, pes_nome from tur_turma, prof_professor, pes_pessoa where prof_professor_prof_id = prof_id and pes_pessoa_pes_id = pes_id and tur_dia1 = '" + dia + "';";
        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        //Class.forName("com.mysql.jdbc.Driver");
        conexao = DriverManager.getConnection(url, "root", "dados");
        //System.out.println("2 - " + select_ano);

        PreparedStatement stmt = conexao.prepareStatement(select_ano);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            
                for (int i = 1; i < 3; i++) {
                    result.add(rs.getString(i));
                }    // System.out.println(rs.getString(1));
            }

            conexao.close();
            return result;
        }



    }
